package lumis.service.comment;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Locale;

import javax.persistence.LockModeType;

import lumis.content.ContentManagerFactory;
import lumis.content.core.Content;
import lumis.content.core.ContentLocale;
import lumis.content.core.ContentRenderDataChangedEvent;
import lumis.content.core.ContentVersion;
import lumis.content.core.IContentManager;
import lumis.doui.processaction.ProcessActionHandler;
import lumis.portal.PortalException;
import lumis.portal.UnexpectedException;
import lumis.portal.authentication.SessionConfig;
import lumis.portal.dao.jdbc.ITransactionJdbc;
import lumis.portal.manager.ManagerFactory;
import lumis.util.ITransaction;
import lumis.util.PortalUtil;

/**
 * Updates the comments total information for a given targetContentLocaleId.
 *
 * @version $Revision: 9601 $ $Date: 2008-07-18 15:38:17 -0300 (Fri, 18 Jul 2008) $
 * @since 4.0.10
 */
public class SetCommentTotalInfoProcessActionHandler extends ProcessActionHandler
{
	/**
	 * It gets targetContentLocaleId parameter and make a call for
	 * {@link #updateCommentTotal(String, String, ITransaction)}. 
	 * 
	 * @version $Revision: 9601 $ $Date: 2008-07-18 15:38:17 -0300 (Fri, 18 Jul 2008) $
	 * @since 4.0.10
	 */
	public void processAction() throws PortalException
	{
		String targetContentLocaleId = (String)getParameter("targetContentLocaleId");
		updateCommentTotal(douiContext.getRequest().getServiceInstanceConfig().getId(), 
				targetContentLocaleId, transaction);
		addDefaultResponse();
	}
	
	/**
	 * Updates the <code>lum_CommentTotal</code> table entry for the 
	 * specified targetContentLocaleId and comment service instance.
	 * @param serviceInstanceId the identifier of the comment service instance 
	 * on which the update is to be done.
	 * @param targetContentLocaleId the id of the commented contentLocale.
	 * @param transaction the transaction for persistence access.
	 * @since 4.2.0
	 */
	public static void updateCommentTotal(String serviceInstanceId, String targetContentLocaleId, ITransaction transaction) throws PortalException
	{
		try
		{
			if(targetContentLocaleId != null)
			{
				// since this operation may not be executed simultaneously,
				// use a lock on contentLocale to synchronize it
				IContentManager contentManager = ContentManagerFactory.getContentManager();
				ContentLocale commentedContentLocale = contentManager.getContentLocale(targetContentLocaleId);
				ManagerFactory.getEntityManager().lock(commentedContentLocale, LockModeType.READ);
				
				ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
				Connection connection = daoTransactionJdbc.getConnection();

				// read values to set in commentTotal
				PreparedStatement selectStatement = connection.prepareStatement(
						"SELECT COUNT(*), SUM(lum_Comment.grade) " + 
						"FROM lum_Content INNER JOIN " + 
						"lum_ContentLocale ON lum_Content.id = lum_ContentLocale.contentId INNER JOIN " + 
						"lum_ContentVersion ON lum_ContentLocale.id = lum_ContentVersion.contentLocaleId INNER JOIN " + 
						"lum_Comment ON lum_ContentVersion.itemId = lum_Comment.commentId " + 
						"WHERE lum_Content.serviceInstanceId = ? AND " +
						"lum_Comment.targetContentLocaleId = ? AND lum_Comment.status = 1");
				try
				{
					selectStatement.setString(1, serviceInstanceId);
					selectStatement.setString(2, targetContentLocaleId);
					
					ResultSet rs = selectStatement.executeQuery();
					try
					{
						int count = 0;
						double avgGrade = 0;
						if (rs.next())
						{
							count = rs.getInt(1);
							avgGrade = rs.getDouble(2) / count;
						}

						if (count > 0)
						{
							// update existent commentTotal
							PreparedStatement updateStatement = connection.prepareStatement(
									"update lum_CommentTotal set averageGrade = ?, numberOfComments = ? " +
									"where targetContentLocaleId = ? and id in (" +
									"select v.itemId from lum_ContentVersion v inner join " +
									"lum_ContentLocale l on v.contentLocaleId = l.id inner join " +
									"lum_Content c on l.contentId = c.id where " +
									"c.serviceInstanceId = ?)");
							try
							{
								updateStatement.setDouble(1, avgGrade);
								updateStatement.setInt(2, count);
								updateStatement.setString(3, targetContentLocaleId);
								updateStatement.setString(4, serviceInstanceId);
								int updateCount = updateStatement.executeUpdate();
								
								// if no corresponding commentTotal entry exists
								if (updateCount == 0)
								{
									// create a new commentTotal with those values
									String commentTotalId = PortalUtil.generateNewGuid();
									PreparedStatement insertStatement = connection.prepareStatement(
											"insert into lum_CommentTotal(id, targetContentLocaleId, averageGrade, numberOfComments) values (?, ?, ?, ?)");
									try
									{
										insertStatement.setString(1, commentTotalId);
										insertStatement.setString(2, targetContentLocaleId);
										insertStatement.setDouble(3, avgGrade);
										insertStatement.setInt(4, count);
										insertStatement.executeUpdate();
									}
									finally
									{
										insertStatement.close();
									}
									
									// create corresponding content
									Content content = new Content();
									content.setSourceId("lum_CommentTotal");
									content.setServiceInstanceId(serviceInstanceId);
									Locale locale = SessionConfig.getCurrentSessionConfig().getLocale();
									content.setDefaultLocale(locale);
									ContentLocale contentLocale = content.addContentLocale(locale);
									ContentVersion contentVersion = contentLocale.addVersion();
									contentVersion.setItemId(commentTotalId);
									contentLocale.setPublishedVersion(contentVersion);
									contentManager.addContent(content);
								}
							}
							finally
							{
								updateStatement.close();
							}
						}
						else
						{
							deleteCommentTotal(serviceInstanceId, targetContentLocaleId, connection);
						}
					}
					finally
					{
						rs.close();
					}
				}
				finally
				{
					selectStatement.close();
				}
				
				// send render data changed event for the content being commented
				ContentVersion publishedVersion = commentedContentLocale.getPublishedVersion();
				if (publishedVersion != null)
				{
					ContentRenderDataChangedEvent event = new ContentRenderDataChangedEvent(
							SessionConfig.getCurrentSessionConfig(), publishedVersion, transaction);
					ManagerFactory.getPortalEventManager().notifyObservers(event);
				}
			}
		}
		catch(SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}

	/**
	 * Deletes a commentTotal entry relative to specified content locale and 
	 * comment service instance.
	 * @param serviceInstanceId the comment service instance identifier.
	 * @param targetContentLocaleId the content locale identifier.
	 * @param connection the connection for persistence access.
	 * @since 4.2.0
	 */
	private static void deleteCommentTotal(String serviceInstanceId,
			String targetContentLocaleId, Connection connection) throws SQLException, PortalException
	{
		// get commentTotal identifier
		PreparedStatement selectIdStatement = connection.prepareStatement(
				"select id from lum_CommentTotal where targetContentLocaleId = ? and id in (" +
				"select v.itemId from lum_ContentVersion v inner join " +
				"lum_ContentLocale l on v.contentLocaleId = l.id inner join " +
				"lum_Content c on l.contentId = c.id where " +
				"c.serviceInstanceId = ?)");
		try
		{
			selectIdStatement.setString(1, targetContentLocaleId);
			selectIdStatement.setString(2, serviceInstanceId);
			ResultSet rsId = selectIdStatement.executeQuery();
			try
			{
				while (rsId.next())
				{
					String commentTotalId = rsId.getString(1);
					
					// delete existent commentTotal
					PreparedStatement deleteStatement = connection.prepareStatement(
							"delete from lum_CommentTotal where id = ?");
					try
					{
						deleteStatement.setString(1, commentTotalId);
						deleteStatement.executeUpdate();
					}
					finally
					{
						deleteStatement.close();
					}
					
					// delete commentTotal's content
					IContentManager contentManager = ContentManagerFactory.getContentManager();
					Content content = contentManager.getContentByItemId(commentTotalId);
					contentManager.deleteContent(content);
				}
			}
			finally
			{
				rsId.close();
			}
		}
		finally
		{
			selectIdStatement.close();
		}
	}
}
